package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"time"
)

//数据库连接信息
const (
	USERNAME = "root"
	PASSWORD = "123456"
	NETWORK = "tcp"
	SERVER = "127.0.0.1"
	PORT = 3306
	DATABASE = "test"
)

//user表结构体定义
type User struct {
	Id int `json:"id" form:"id"`
	Username string `json:"username" form:"username"`
	Password string `json:"password" form:"password"`
}

func main() {
	conn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s",USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
	DB, err := sql.Open("mysql", conn)
	if err != nil {
		fmt.Println("connection to mysql failed:", err)
		return
	}

	DB.SetConnMaxLifetime(100*time.Second)  //最大连接周期，超时的连接就close
	DB.SetMaxOpenConns(100)                //设置最大连接数
	CreateTable(DB,DATABASE)
	InsertData(DB,"zhangsan","123456")
	InsertData(DB,"lisi","654321")
}

func CreateTable(DB *sql.DB,tableName string) {
	sql := `CREATE TABLE IF NOT EXISTS users(
	id INT(4) PRIMARY KEY AUTO_INCREMENT NOT NULL,
	username VARCHAR(64),
	password VARCHAR(64)
	); `

	if _, err := DB.Exec(sql); err != nil {
		fmt.Println("create table failed:", err)
		return
	}
	fmt.Println("create table successd")
}

//插入数据
func InsertData(DB *sql.DB,Name string,Password string) {
	result,err := DB.Exec("insert INTO users(username,password) values(?,?)",Name,Password)
	if err != nil{
		fmt.Printf("Insert data failed,err:%v", err)
		return
	}
	lastInsertID,err := result.LastInsertId()    //获取插入数据的自增ID
	if err != nil {
		fmt.Printf("Get insert id failed,err:%v", err)
		return
	}
	fmt.Println("Insert data id:", lastInsertID)

	rowsaffected,err := result.RowsAffected()  //通过RowsAffected获取受影响的行数
	if err != nil {
		fmt.Printf("Get RowsAffected failed,err:%v",err)
		return
	}
	fmt.Println("Affected rows:", rowsaffected)
}

//查询单行
func QueryOne(DB *sql.DB,queriedUserName string) {
	user := new(User)   //用new()函数初始化一个结构体对象
	row := DB.QueryRow("select id,username,password from users where username=?",queriedUserName)
	//row.scan中的字段必须是按照数据库存入字段的顺序，否则报错
	if err := row.Scan(&user.Id,&user.Username,&user.Password); err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Println("Single row data:", *user)
}
